import requests
import pymysql
import lxml.html
import time

etree = lxml.html.etree

#第一次爬

class ZhiCityProOne:

    #初始化函数
    def __init__(self):
        self.header = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.111 Safari/537.36',
            'Content-type': 'text/html',
            'charset': 'utf-8'}
        self.url = "http://www.taskcity.com/projects/skills"

    #获取文件信息
    def get_project(self):

        conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
        cur = conn.cursor()

        for i in range(1,761):

            response = requests.get(self.url,params={"page":str(i)},headers = self.header)

            response.encoding="utf-8"

            if response.status_code == 200:
                html = etree.HTML(response.text)
                for j in range(1,31):

                    #print("**************************************")
                    els = html.xpath("/html/body/div[1]/div[2]/div/div[1]/div["+str(j)+"]/div/div[1]/h4/a/@href")
                    key = str(els)[12:-2]
                    realkey = int(key)
                    #print("key:"+key)

                    els = html.xpath("/html/body/div[1]/div[2]/div/div[1]/div["+str(j)+"]/div/div[1]/h4/a/@title")
                    name = str(els)[2:-2]
                    #print("name:"+name)

                    els = html.xpath("/html/body/div[1]/div[2]/div/div[1]/div["+str(j)+"]/div/div[2]/p/span[1]/text()")
                    status = str(els)[2:-2]
                    #print("status:"+status)

                    els = html.xpath("/html/body/div[1]/div[2]/div/div[1]/div["+str(j)+"]/div/div[2]/p/span[2]/text()")
                    alltime = str(els)[9:-2]
                    #print("alltime:"+alltime)

                    els = html.xpath("/html/body/div[1]/div[2]/div/div[1]/div["+str(j)+"]/div/div[2]/p/a/text()")
                    num = str(els)[2:-2]
                    realnum = int(num)
                    #print("num:"+num)

                    #print("++++++++++++++++++++++++++++++++++++")
                    #print()

                    #标记插入操作是否成功
                    #flag = 0

                    #sql = "INSERT INTO project(MASK, STATUS, NAME, TIME, NUM) VALUES(" + str(key) + ", '" + status + "', '" + name + "', '" + time + "', " + str(num) + ")"
                    #print(sql)

                    #尝试insert
                    # noinspection PyBroadException
                    try:

                        testsql = "select count(pid) from project where pid = %s limit 1" % (realkey)
                        cur.execute(testsql)

                        if cur.fetchone()[0] > 0:
                            #print("update")
                            sql = "update project set status = '%s' ,name = '%s' ,date = '%s' ,num = %d WHERE pid = %d" % (
                            status, name, alltime, realnum, realkey)
                        else:
                            #print("insert")
                            sql = "insert into project (pid, status, name, date, num) VALUES(%d, '%s', '%s', '%s', %d)" % (
                            realkey, status, name, alltime, realnum)


                        #cur.execute(sql)
                        #cur.execute('INSERT INTO project (MASK, STATUS, NAME, TIME, NUM) VALUES(%d, %s, %s, %s, %d)',(realkey,status,name,time,realnum))

                        print(sql)
                        cur.execute(sql)
                        conn.commit()
                        #print(str(cur.fetchone()))
                        #flag = 1
                    except:
                        #若插入不成功则尝试更新数据
                        #print("error")
                        #sql = "UPDATE project SET STATUS = '"+status+"' ,name = '"+name+"' ,time = '"+alltime+"' ,num = "+num+" WHERE MASK = "+ key
                        #print(sql)
                        #cur.execute('UPDATE project SET STATUS = %s ,NAME = %s ,TIME = %s ,NUM = %d WHERE MASK = %d',(status,name,time,realnum,realkey))
                        #conn.commit()
                        #conn.rollback()
                        print("error!")

            time.sleep(30)

        #关掉，关掉，数据库链接一定要记得关掉
        cur.close()
        conn.close()

if __name__ in "__main__":
    cls = ZhiCityProOne()
    cls.get_project()
